package com.qingcheng.dao;

import com.qingcheng.pojo.goods.Spu;
import org.apache.ibatis.annotations.Select;
import org.springframework.web.bind.annotation.RequestParam;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

public interface SpuMapper extends Mapper<Spu> {


    /*
     *热卖商品的信息(完整版)
     */
    @Select("SELECT spu.id spuId,oi.spu_id,sku.*,MIN(sku.price) money  FROM qingcheng_order.tb_order o \n" +
            "INNER JOIN qingcheng_order.tb_order_item oi \n" +
            "INNER JOIN tb_spu spu\n" +
            "INNER JOIN tb_sku sku \n" +
            "ON o.id=oi.order_id\n" +
            "WHERE o.create_time>= #{date1} \n" +
            "AND o.create_time<= #{date2} \n" +
            "AND spu.id=oi.spu_id\n" +
            "AND spu.id=sku.spu_id\n" +
            "AND spu.is_marketable='1'\n" +
            "AND o.pay_status='1'\n" +
            "GROUP BY oi.spu_id\n" +
            "ORDER BY SUM(oi.num) DESC\n" +
            "LIMIT 4\n")
    public List<Map> bestgoods(@RequestParam("date1")LocalDate date1,@RequestParam("date1")LocalDate date2);


    @Select("SELECT sku.*,spu.id spuId,MIN(sku.price) money FROM tb_spu spu INNER JOIN tb_sku sku \n" +
            "WHERE spu.id=sku.spu_id\n" +
            "AND spu.is_marketable='1'\n" +
            "GROUP BY sku.id\n" +
            "ORDER BY RAND() \n" +
            "LIMIT 4\n")
    public List<Map> nullbestgoods();


    /*
    * 最新上架商品
    */
    @Select("SELECT spu.id spuid,sku.*,MIN(sku.price) money FROM tb_spu spu INNER JOIN tb_sku sku \n" +
            "ON spu.id=sku.spu_id\n" +
            "WHERE spu.is_marketable='1'\n" +
            "AND sku.status='1'\n" +
            "GROUP BY sku.spu_id\n" +
            "ORDER BY create_time DESC\n" +
            "LIMIT 4")
    List<Map> newproduct();

    /*
    * 推荐喜欢的分类id
    * */
    @Select("SELECT oi.category_id3 FROM qingcheng_user.tb_user u INNER JOIN qingcheng_order.tb_order o\n" +
            "INNER JOIN qingcheng_order.tb_order_item oi\n" +
            "ON u.username=o.username\n" +
            "WHERE o.id=oi.order_id\n" +
            "GROUP BY oi.category_id3\n" +
            "ORDER BY SUM(oi.num) DESC\n" +
            "LIMIT 2")
    List<String> likeid();

    /*
    * 查询商品
    * */
    @Select("SELECT spu.id spuid,spu.name name,sku.image image,MIN(sku.price) money FROM tb_category cate INNER JOIN tb_spu spu \n" +
            "INNER JOIN tb_sku sku \n" +
            "ON cate.id=spu.category3_id\n" +
            "WHERE spu.id=sku.spu_id\n" +
            "AND cate.id = #{id} \n" +
            "AND spu.is_marketable='1'\n" +
            "GROUP BY spu.id\n" +
            "ORDER BY RAND() LIMIT 3")
    List<Map> like(@RequestParam("id") String id);



    @Select("SELECT spu.id spuid,spu.name name,sku.image image,MIN(sku.price) money FROM tb_spu spu " +
            "INNER JOIN tb_sku sku \n" +
            "WHERE spu.id=sku.spu_id\n" +
            "AND spu.is_marketable='1'\n" +
            "GROUP BY spu.id\n" +
            "ORDER BY RAND() LIMIT 6")
    List<Map> nullLike();

}
